
DEClARE @pTABLE_OBJECT_ID INT
--,@pTABLE_NAME VARCHAR(80)
--SET @pTABLE_NAME='ENTRY_CLIV_INVC_INFO'

SELECT @pTABLE_OBJECT_ID= object_id from sys.objects obj
where name=@pTABLE_NAME and type='U'

IF ISNULL(@pTABLE_OBJECT_ID,0)=0
BEGIN
    
	RETURN
END 


 
select
    col.column_id,
	col.name as column_name,
	col.max_length as column_length,
	col.is_nullable as nullable,
	t.name as column_type,
	isnull(ep.value,'') as column_desc,
	isnull(ktable.is_primary_key,0) is_key
from sys.objects obj
join sys.columns col on obj.object_id=col.object_id
left join (select  ind.is_primary_key,
					ic.index_id,
					ic.column_id
			from sys.index_columns ic
			 join sys.indexes ind
			on ic.object_id=ind.object_id
			and ic.index_id=ind.index_id
			and ind.name like 'PK_%'
			where ic.object_id= @pTABLE_OBJECT_ID) as  ktable on ktable.column_id= col.column_id
join sys.types t on t.user_type_id=col.user_type_id
left join sys.extended_properties ep on ep.major_id=obj.object_id and ep.minor_id=col.column_id and ep.name='MS_Description'
where obj.object_id= @pTABLE_OBJECT_ID 
 AND NOT EXISTS(SELECT 1 FROM sys.extended_properties  WHERE major_id=obj.object_id and minor_id=col.column_id 
                  and name='ORM_ENABLE' AND value='N' )  
--isnull(eb.value,'')<>'N'

SELECT name as table_name,value as table_value 
FROM sys.extended_properties ep 
where ep.major_id= @pTABLE_OBJECT_ID AND ep.minor_id=0

SELECT minor_id as column_id, col.name as column_name,ep.name,ep.value
FROM sys.extended_properties ep
join sys.columns col on col.object_id=@pTABLE_OBJECT_ID and ep.minor_id= col.column_id
WHERE ep.major_id = @pTABLE_OBJECT_ID --AND ep.minor_id= col.column_id
     and ep.name<>'MS_Description'



